package com.zhuiyun.project.api.submitrecord.mapper;


import com.zhuiyun.project.api.submitrecord.entity.SubmitRecord;
import com.zhuiyun.project.api.sysUser.entiy.SysUser;
import com.zhuiyun.project.common.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

public interface SubmitMapper extends BaseMapper<SubmitRecord> {



    /**
     * 获取自己的提交记录
     * @param phone
     * @return
     */
    @Select("select * from submit_record where phone_number =#{phone} order by lottery_type_index, project_name_index, lottery_no desc")
    List<SubmitRecord> getMySubmitRecord(String phone);

    /**
     * 按照中奖频率的高低查找推荐数据
     * @param lotteryTypeIndex
     * @return
     */
    @Select("select s.phoneNumber,flag1,flag0,s.userName,#{lotteryNo} as lotteryNo  from (SELECT\n" +
            "\ts.phone_number AS phoneNumber ,\n" +
            "\tSUM(CASE WHEN s.flag =1 THEN 1 ELSE 0 END) as flag1 ,\n" +
            "\tSUM(CASE WHEN s.flag =0 THEN 1 ELSE 0 END) as flag0,\n" +
            "\tu.user_name AS userName \n" +
            "FROM\n" +
            "\tsubmit_record s,\n" +
            "\tsys_user u \n" +
            "WHERE\n" +
            "\tu.phone = s.phone_number \n" +
            "\tAND s.lottery_type_index =  #{lotteryTypeIndex}\n" +
            "\tAND s.project_name_index = #{projectNameIndex} \n" +
            "\n" +
            "\tAND s.create_time >= DATE_SUB( CURDATE(), INTERVAL 1 MONTH ) \n" +
            "GROUP BY\n" +
            "\ts.phone_number \n" +
            "ORDER BY\n" +
            "\tflag1 DESC)   as s  where s.phoneNumber in ( \n" +
            "\tselect phone_number from submit_record where  lottery_no = #{lotteryNo} AND lottery_type_index =  #{lotteryTypeIndex} " +
            "   AND project_name_index = #{projectNameIndex}  GROUP BY phone_number " +
            ") ORDER BY s.flag1 desc ")
    List<Map> getSubmitRecordByType(Integer lotteryTypeIndex, Integer projectNameIndex,String lotteryNo);

    @Select("SELECT s.phone_number as phone,s.numbers,s.record,s.project_name_index,s.lottery_type_index,s.type,s.profit,s.id,s.status,s.result as result,s.lottery_no as lotteryNo , s.flag ,s.count as count ,s.commission,u.user_name as userName from submit_record s ,sys_user u where s.phone_number = u.phone and " +
            " s.lottery_type_index = #{lotteryTypeIndex} and s.project_name_index = #{projectNameIndex} and s.phone_number =  #{phoneNumber} " +
            " and s.create_time  >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)   ORDER BY lotteryNo desc")
    List<Map> getSubmitRecordByPhoneAndType(Integer lotteryTypeIndex, Integer projectNameIndex, String phoneNumber);


    @Select("\tSELECT\n" +
            "\t\ts.phone_number AS phoneNumber,\n" +
            "\t\tSUM( CASE WHEN s.flag = 1 THEN 1 ELSE 0 END ) AS flag1,\n" +
            "\t\tSUM( CASE WHEN s.flag = 0 THEN 1 ELSE 0 END ) AS flag0,\n" +
            "\t\tcount(s.flag) as count \n" +
            "\tFROM\n" +
            "\t\tsubmit_record s,\n" +
            "\t\tsys_user u \n" +
            "\tWHERE\n" +
            "\t\tu.phone = s.phone_number and s.phone_number = #{phone}\n" +
            "\t\tAND s.create_time >= DATE_SUB( CURDATE(), INTERVAL 1 MONTH ) \n" +
            "\tGROUP BY\n" +
            "\t\ts.phone_number \n" +
            "\tORDER BY\n" +
            "\t\tflag1 DESC \n" +
            "\t")
    Map getMyExperience(String phone);

    /**
     * 查询今天是否发布过同类型项目
     * @param phone
     * @param lotteryTypeIndex
     * @param projectNameIndex
     * @return
     */
    @Select("select count(1) from submit_record where phone_number =#{phone} and lottery_type_index = #{lotteryTypeIndex} and project_name_index = #{projectNameIndex} and DATE(create_time) = CURDATE();")
    int selectCountByPhoneAndTypeIndexAndProjectIndex(String phone, Integer lotteryTypeIndex, Integer projectNameIndex);

    /**
     * 获取最新的推荐号码
     * @param type
     * @param lastLotteryNo
     * @return
     */
    @Select("select * from submit_record where type=#{type} and lottery_no = #{lastLotteryNo}")
    List<SubmitRecord> getLastSubmit(String type, String lastLotteryNo);

    /**
     * 增加查看数
     * @param id
     * @return
     */
    @Update("update submit_record set count = count+1 where id = #{id}")
    int updateCountIncr(Integer id);

    /**
     * 按照中奖频率的高低查找中奖数据
     * @param lotteryTypeIndex
     * @return
     */
    @Select("select s.phoneNumber,flag1,flag0,s.userName,#{lotteryNo} as lotteryNo  from (SELECT\n" +
            "\ts.phone_number AS phoneNumber ,\n" +
            "\tSUM(CASE WHEN s.flag =1 THEN 1 ELSE 0 END) as flag1 ,\n" +
            "\tSUM(CASE WHEN s.flag =0 THEN 1 ELSE 0 END) as flag0,\n" +
            "\tu.user_name AS userName \n" +
            "FROM\n" +
            "\tsubmit_record s,\n" +
            "\tsys_user u \n" +
            "WHERE\n" +
            "\tu.phone = s.phone_number \n" +
            "\tAND s.lottery_type_index =  #{lotteryTypeIndex}\n" +
            "\tAND s.project_name_index = #{projectNameIndex} \n" +
            "\n" +
            "\tAND s.create_time >= DATE_SUB( CURDATE(), INTERVAL 1 MONTH ) \n" +
            "GROUP BY\n" +
            "\ts.phone_number \n" +
            "ORDER BY\n" +
            "\tflag1 DESC)   as s  where s.phoneNumber in ( \n" +
            "\tselect phone_number from submit_record where  lottery_no = #{lotteryNo} AND lottery_type_index =  #{lotteryTypeIndex} " +
            "   AND project_name_index = #{projectNameIndex} and flag =1 GROUP BY phone_number " +
            ") ORDER BY s.flag1 desc ")
    List<Map> getSubmitRecordByTypeAndToday(Integer lotteryTypeIndex, Integer projectNameIndex, String lotteryNo);
}